<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8" />
  <title>Guestbook</title>
  <style>
    body {
      font-family: sans-serif;
      max-width: 600px;
      margin: 2rem auto;
    }

    #comments {
      border: 1px solid #ddd;
      border-radius: 6px;
      padding: 0;
      list-style: none;
    }

    #comments li {
      padding: 8px;
      border-top: 1px solid #eee;
    }

    #comments li:first-child {
      border-top: none;
    }

    .time {
      font-size: 0.8em;
      color: gray;
    }
  </style>
</head>

<body>
  <h1>Guestbook</h1>
  <form id="form">
    <input id="comment" placeholder="Write a comment..." style="width:70%; padding:6px;" />
    <button type="submit">Add</button>
  </form>
  <ul id="comments"></ul>

  <script type="module">
    import { connect } from "@tursodatabase/database-wasm/vite";

    let db = await connect("local.db", {
      timeout: 1000 // busy timeout for handling high-concurrency write cases
    });
    // execute multiple SQL statements with exec(...)
    await db.exec(`
      CREATE TABLE IF NOT EXISTS guestbook ( comment TEXT, created_at DEFAULT (unixepoch()) );
      CREATE INDEX IF NOT EXISTS guestbook_idx ON guestbook (created_at);
    `);
    await refresh();

    async function addComment() {
      // use prepared statements and bind args to placeholders later
      const insert = db.prepare(`INSERT INTO guestbook(comment) VALUES (?)`);

      const input = document.getElementById("comment");
      const text = input.value.trim();
      input.value = "";

      // use run(...) method if query only need to be executed till completion
      await insert.run([text]);

      await refresh();
    }
    document.getElementById("form").addEventListener("submit", (e) => {
      e.preventDefault();
      addComment();
    });

    async function refresh() {
      const select = db.prepare(`SELECT comment, created_at FROM guestbook ORDER BY created_at DESC LIMIT ?`);
      // use all(...) or get(...) methods to get all or one row from the query
      const rows = await select.all([5]);

      const ul = document.getElementById("comments");
      ul.innerHTML = "";
      rows.forEach((row) => {
        const li = document.createElement("li");
        li.innerHTML = `
              <div>${row.comment}</div>
              <div class="time">${new Date(row.created_at * 1000).toLocaleString()}</div>
            `;
        ul.appendChild(li);
      });
    }
  </script>
</body>

</html>